from fastapi import FastAPI, Request, Form, HTTPException
from fastapi.templating import Jinja2Templates
from fastapi.responses import RedirectResponse
from psycopg.errors import UniqueViolation, ForeignKeyViolation

from dblock import create_dpool

dblock = create_dpool("host=localhost dbname=examdb user=examdb", min_size=4)

app = FastAPI()
templates = Jinja2Templates(directory="./")


@app.get("/")
async def view_list_grades(request: Request):
    with dblock() as db:
        db.execute("""
        SELECT g.stu_sn, g.cou_sn, 
            s.name as stu_name, 
            c.name as cou_name, 
            g.grade 
        FROM course_grade as g
            INNER JOIN student as s ON g.stu_sn = s.sn
            INNER JOIN course as c  ON g.cou_sn = c.sn
        ORDER BY stu_sn, cou_sn;
        """)

        items = list(db)

    return templates.TemplateResponse(
        "grade_list.html",
        {
            "request": request,
            "items": items,
        },
    )


@app.get("/grade/edit/{stu_sn}/{cou_sn}")
def view_grade_editor(stu_sn: int, cou_sn: int, request: Request):
    with dblock() as db:
        db.execute(
            """
            SELECT grade FROM course_grade
                WHERE stu_sn = %(stu_sn)s AND cou_sn = %(cou_sn)s;
            """,
            dict(stu_sn=stu_sn, cou_sn=cou_sn),
        )

        record = db.fetchone()

    if record is None:
        errmsg = f"无此学生{stu_sn}的课程{cou_sn}成绩"
        raise HTTPException(status_code=404, detail=errmsg)

    return templates.TemplateResponse(
        "grade_edit.html",
        {"request": request, "stu_sn": stu_sn, "cou_sn": cou_sn, "grade": record.grade},
    )


@app.post("/action/grade/add")
async def action_grade_add(
    stu_sn: int = Form(...), cou_sn: int = Form(...), grade: float = Form(...)
):
    try:
        with dblock() as db:
            db.execute(
                """
                INSERT INTO course_grade (stu_sn, cou_sn, grade) 
                VALUES ( %(stu_sn)s, %(cou_sn)s, %(grade)s)
                """,
                dict(stu_sn=stu_sn, cou_sn=cou_sn, grade=grade),
            )
    except UniqueViolation:
        errmsg = f"学生{stu_sn}的课程{cou_sn}成绩已添加"
        raise HTTPException(status_code=400, detail=errmsg)
    except ForeignKeyViolation as ex:
        errmsg = f"无此学生或课程: {ex}"
        raise HTTPException(status_code=400, detail=errmsg)

    return RedirectResponse(url="/", status_code=302)


@app.post("/action/grade/edit/{stu_sn}/{cou_sn}")
async def edit_grade_action(stu_sn: int, cou_sn: int, grade: float = Form(...)):
    with dblock() as db:
        db.execute(
            """
            UPDATE course_grade SET grade=%(grade)s
            WHERE stu_sn = %(stu_sn)s AND cou_sn = %(cou_sn)s
            """,
            dict(stu_sn=stu_sn, cou_sn=cou_sn, grade=grade),
        )

    return RedirectResponse(url="/", status_code=302)


@app.post("/action/grade/delete/{stu_sn}/{cou_sn}")
def delete_grade_action(stu_sn: int, cou_sn: int):
    with dblock() as db:
        db.execute(
            """
            DELETE FROM course_grade
                WHERE stu_sn = %(stu_sn)s AND cou_sn = %(cou_sn)s
            """,
            dict(stu_sn=stu_sn, cou_sn=cou_sn),
        )

    return RedirectResponse(url="/", status_code=302)
